package cn.puppy.postgres.demo.util;


import java.io.FileInputStream;
import java.io.IOException;
import java.net.InetAddress;
import java.net.URLDecoder;
import java.net.UnknownHostException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import cn.puppy.postgres.demo.domain.RowData;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;



/**
 *
 * @Title: Access.java
 * @Package: com.luli.lab
 * @Description: connect 2 access DB
 * @author: zhanglimin
 * @date: 2021年41月12日 16:47:12
 * @version: V-1.0.0
 * @Copyright: 2021 zhanglm
 *
 */
public class Access {

	/**
	 * Access数据库Connection
	 */
	private static Connection connection;
	//配置log4j.properties， 并放入项目的根路径下.(也可以放入其他路径，在读的时候需要指定)
	private static final Logger logger = LoggerFactory.getLogger(Access.class);

	/**
	 * Java 获取 Access 数据库连接(Connection)
	 * <table border="1" cellpadding="8">
	 * <tr><td colspan="2" align="center">Java 获取  Access 数据库连接(Connection) 注释 </td></tr>
	 * <tr><th>输入参数</th><th>参数解释</th></tr>
	 * <tr><td align="center">path</td><td>Access文件的相对或者绝对路径(支持*.mdb和*.accdb数据库文件)</td></tr>
	 * <tr><td align="center">user</td><td>用户账号(如果没有就写"")</td></tr>
	 * <tr><td align="center">pwd</td><td>密码密码(如果没有就写"")</td></tr>
	 * </table>
	 * @param path Access文件的相对或者绝对路径(支持*.mdb和*.accdb数据库文件)
	 * @param user 用户账号(如果没有就写"")
	 * @param pwd  密码密码(如果没有就写"")
	 * @return: Access 的 Connection
	 * @date: 2021年4月18日 12:47:12
	 * @author 張立民
	 */
	private static Connection getAccessConnection(String path, String user, String pwd) {
		try {
			//获取Access数据库连接(Connection)
//			Properties prop = new Properties();
//			prop.load(new FileInputStream("resources\\lab_AR_AD.properties"));
//			String ip = prop.getProperty("ip");
			logger.error("error>>>>时间：{},错误：{}",new Date(),"access服务器连接断开");
			/**
			 * ping通了，然后连接的时候电脑关闭了，就废了，需要用try catch，也没用。放弃。
			 */
//			try {
//				connection = DriverManager.getConnection("jdbc:ucanaccess://" + path, user, pwd);
//			} catch (Exception e) {
////				Exception ex = new Exception("又又又关机了！！！！！");
////				ex.printStackTrace();
//				logger.error("error>>>>时间：{},错误：{}",new Date(),"access服务器连接断开");
////				e.printStackTrace();
//				boolean pingIP = pingIP(ip);
//				if(pingIP) {
//					connection = DriverManager.getConnection("jdbc:ucanaccess://" + path, user, pwd);
//				}else {
//					while(!pingIP) {
//						System.out.println("ping " + ip + "......... time out");
//						pingIP = pingIP(ip);
//					}connection = DriverManager.getConnection("jdbc:ucanaccess://" + path, user, pwd);
//				}
//			}


		} catch (Exception e) {
//			throw new RuntimeException(e.getMessage());
			e.printStackTrace();
		}
		return connection;
	}

	public static void main(String[] args) throws Exception {
		Access access=new Access();
		Properties prop = new Properties();
		prop.load(new FileInputStream("resources\\lab_AR_AD.properties"));
//		String path = "\\\\192.168.56.1\\lab2ortary\\gf.mdb";
		String path = prop.getProperty("path");
//		prop.load(new FileInputStream(file));
//		Connection connection = access.getAccessConnection(path, "", "");
		access.select("7");
	}

	/**
	 * Access插入(使用了预编译)
	 *
	 * @param connection 连接
	 * @return 受影响的行数
	 * @throws Exception 异常
	 * @date: 2021年41月12日 16:47:12
	 */
	public int insert(Connection connection) throws Exception {
		// ? 是 JDBC 预编译的占位符
		PreparedStatement statement=connection.prepareStatement("insert into student(id,name,address,age) values(?,?,?,?)");
		statement.setInt(0, 1);//学生编号
		statement.setString(1, "赵六");//学生姓名
		statement.setString(2, "湖南省、衡阳市、珠晖区1");//学生住址
		statement.setInt(3, 20);//学生年龄
		int result = statement.executeUpdate();
		statement.close();
		connection.close();
		return result;
	}

	/**
	 * Access删除
	 *
	 * @param connection 连接
	 * @return 受影响的行数
	 * @throws Exception 异常
	 * @date: 2021年41月12日 16:47:12
	 */
	public int delete(Connection connection) throws Exception {
		Statement statement = connection.createStatement();
		int result = statement.executeUpdate("delete from student where id=3");
		statement.close();
		connection.close();
		return result;
	}

	/**
	 * Access更新
	 *
	 * @param connection 连接
	 * @return 受影响的行数
	 * @throws Exception 异常
	 * @date: 2021年41月12日 16:47:12
	 */
	public int update(Connection connection) throws Exception {
		Statement statement = connection.createStatement();
		int result = statement.executeUpdate("update student set address='湖南省、衡阳市、珠晖区' where id=1");
		statement.close();
		connection.close();
		return result;
	}

	/**
	 * Access查询获取.mdb文件中FResultdata表数据
	 *
	 * @param connection 连接
	 * @throws Exception 异常
	 * @date: 2021年41月12日 16:47:12
	 */
	public List<RowData> select(String days) throws Exception {
		System.out.println("*********");
		String sql = "select * from FResultdata where datediff('d',FinalUpdate,Date())< ?  ";
		sql = sql.replace("?", days);
//		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		List<RowData> list = new ArrayList<RowData>();
		Properties prop = new Properties();
//		prop.load(new FileInputStream("resources\\lab_AR_AD.properties"));
		String path = prop.getProperty("path");
		System.out.println(path);
		connection = getAccessConnection("//D:/YOUXIN/YX-GYFX7705/gyfx.mdb", "", "GYFX");
		Statement statement = connection.createStatement();
		ResultSet result = statement.executeQuery(sql);
		ResultSetMetaData md = result.getMetaData(); //获得结果集结构信息,元数据
		int columnCount = md.getColumnCount();   //获得列数
		while (result.next()) {
		      boolean flag = true;
		      RowData rowData = new RowData();
		      for (int i = 1; i <= columnCount; i++) {

		    	  if(result.getObject(i) != null && md.getColumnName(i).equals("ManualNumber")) {//设置样本号
		    		  rowData.setManualNumber(result.getObject(i).toString());
		    	  }
		    	  if(md.getColumnName(i).equals("Qnet_var_Cal")) {//设置Qnet_var_Cal
		    		  rowData.setQnet_var_Cal(result.getObject(i).toString());
		    	  }
		    	  if(md.getColumnName(i).equals("Qgrd_Cal")) {//设置Qgrd_Cal
		    		  rowData.setQgrd_Cal(result.getObject(i).toString());
		    	  }
		    	  if(md.getColumnName(i).equals("FinalUpdate")) {//设置FinalUpdate
		    		  rowData.setFinalUpdate(result.getObject(i).toString());
		    	  }
		      }
		      list.add(rowData);
		    }
//		RUSDLabDB rusdLabDB = new RUSDLabDB();
//		while (result.next()) {
//			String temp = result.getString(3);
//			if(result.getString(13).equals("9121")) {
//				rusdLabDB.update(result.getString(13),"Vdaf",result.getString(3));
//			}
////			System.out.print(result.getString(1) + "\t");
////			System.out.print(result.getString(2) + "\t");
//			System.out.print(result.getString(3) + "\t");//干基灰分》》》Ad
////			System.out.print(result.getString(4) + "\t");
////			System.out.print(result.getString(5) + "\t");
//			System.out.print(result.getString(6) + "\t");//干燥无灰基挥发份 》》》vdaf
////			System.out.print(result.getString(7) + "\t");
////			System.out.print(result.getString(8) + "\t");
////			System.out.print(result.getString(9) + "\t");
////			System.out.print(result.getString(10) + "\t");
////			System.out.print(result.getString(11) + "\t");
////			System.out.print(result.getString(12) + "\t");
//			System.out.print(result.getString(13) + "\t");//试样名称
//			System.out.println();
//		}
		result.close();
		statement.close();
		connection.close();
		return list;
	}

	public static boolean pingIP(String ip) throws UnknownHostException, IOException{
		//能ping通放回true 反之 false 超时时间 3000毫秒
		return InetAddress.getByName(ip).isReachable(3000);
	}
}


